ALTER proc sp_cancel_lineItem_proc( @lineItem_id int, @user_id varchar(50), @is_cancel int )
AS
declare @db_cancel int
SELECT @db_cancel = (SELECT COOL_Is_Cancel FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @lineItem_id)

begin
    UPDATE Customer_Open_Order_LineItem SET COOL_Is_Cancel = 0, COOL_Status= 'HOLD', COOL_Last_Updated_By_ID = @user_id, COOL_Last_Updated_By_Name = @user_id, Timestamp = CURRENT_TIMESTAMP WHERE LineItem_ID = @lineItem_id
    /*update the holds in sales Hold table as it needs approval from all the approvers*/
    if( @is_cancel <> @db_cancel )
    begin
        UPDATE Sales_Hold_Table SET Hold_Reason_Code = '0', Is_Approved = '1',	Hold_By_User='SYSTEM', Timestamp = CURRENT_TIMESTAMP	WHERE LineItem_ID = @lineItem_id
    end
end





